﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using Citi.GenericApplications.SchedulerUi.EmailSenderWcf;
using Citi.PTSConsolidation.Domain;
using Citi.RoutineBase;
using SqlHelper = Citi.PTSConsolidation.Domain.Utils.SqlHelper;
using Citi.PTSConsolidation.SchedulerUI.Util;

namespace Citi.PTSConsolidation.SchedulerUI
{
    public class ProjectProgramExtraction : IExecutableRoutine
    {
        public IEnumerable<RoutineParameter> Parameters
        {
            get
            {
                return new List<RoutineParameter> { 
                    new RoutineParameter{ Description = "Users to send email about program ids", Name ="EmailUsers", Nullable = false, Required = true, Type = typeof(string)}
                };
            }
        }

        private string PtsUrl { get { return ConfigurationManager.AppSettings["PtsUrl"]; } }
        private string PtsConnectionString { get { return ConfigurationManager.AppSettings["PtsConnectionString"]; } }
        private string ExcelConnectionString { get { return ConfigurationManager.AppSettings["ExcelConnectionString"]; } }
        private string ProjectProgramExcelPath { get { return ConfigurationManager.AppSettings["ProjectProgramExcelPath"]; } }

        public void Rollback(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }

        private void DeletePreviousData(ICurrentAction currentAction)
        {
            currentAction.Description = "Deleting Previous Data";
            var query = ProjectProgramTemp.GetQueryDelete();
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        private void MergeProjects(ICurrentAction currentAction)
        {
            currentAction.Description = "Merging projects";
            var query = ProjectProgramTemp.GetQueryMergeProjects();
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        private void MergePrograms(ICurrentAction currentAction)
        {
            currentAction.Description = "Merging programs";
            var query = ProjectProgramTemp.GetQueryMergePrograms();
            currentAction.Description = query;
            SqlHelper.ExecuteReader(query, PtsConnectionString);
        }

        public void Start(DateTime scheduledDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            var importedPath = Path.Combine(ProjectProgramExcelPath, "Imported");

            if (!Directory.Exists(importedPath))
                Directory.CreateDirectory(importedPath);

            currentAction.Description = string.Format("Looking for file in {0}", ProjectProgramExcelPath);
            var files = Directory.GetFiles(ProjectProgramExcelPath).Where(x => x.EndsWith(".xlsx"));

            if (!files.Any())
            {
                currentAction.Description = "No files were found. The routine is set as Finished.";
                return;
            }

            DeletePreviousData(currentAction);

            foreach (var file in files)
            {
                currentAction.Description = string.Format("{0} found", Path.GetFileName(file));
                currentAction.Description = "Reading file";

                SqlHelper.CopyExcelToSql(string.Format(ExcelConnectionString, file), ProjectProgramTemp.QueryExcel,
                    "ProjectProgramTemp", PtsConnectionString, ProjectProgramTemp.ExcelColumnMappings, "ProgramProject", true);
                
                currentAction.Description = "Moving file to import folder";
                var importedFile = Path.Combine(importedPath, Path.GetFileName(file));

                if (File.Exists(importedFile)) File.Delete(importedFile);
                File.Move(file, importedFile);
            }

            MergePrograms(currentAction);
            MergeProjects(currentAction);
            SendEmail(currentAction, parameters);
        }

        private void SendEmail(ICurrentAction currentAction, Dictionary<string, object> parameters)
        {
            currentAction.Description = "Verifying changes";
            var query = ProjectProgramTemp.GetQueryProgramWithoutOwner();
            currentAction.Description = query;
            var programs = SqlHelper.ExecuteReader(query, PtsConnectionString);

            var emailUsers = parameters["EmailUsers"].ToString().Split(new[] { ';' }).ToList();

            if (programs.Rows.Count > 0)
            {
                currentAction.Description = "Exceptions found";
                var programsIds = string.Empty;

                foreach (DataRow row in programs.Rows)
                {
                    programsIds += string.Format(@"<tr><td style=""width:100px;"">{0}</td><td style=""width:300px;"">{1}</td></tr>",
                    row["Id"].ToString(), row["ProgramName"].ToString());
                }

                var emailParameters = new Dictionary<string, string>();
                emailParameters.Add("[ProgramIds]", programsIds);
                emailParameters.Add("[AssociateLink]", string.Format("{0}/Program/Exceptions", PtsUrl));

                EmailSenderHelper.SendEmailWithTemplate(emailUsers, null, null, 2555, emailParameters);
            }
            else
            {
                currentAction.Description = "Exceptions not found";
                EmailSenderHelper.SendEmailWithTemplate(emailUsers, null, null, 2554, new Dictionary<string, string>());
            }
        }

        public void Stop(ref ICurrentAction currentAction, Dictionary<string, object> parameters)
        {

        }
    }
}
